package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;


import com.ws.shop.bean.Page;
import com.ws.shop.dao.MemberDao;
import com.ws.shop.entity.Address;
import com.ws.shop.entity.Loginlog;
import com.ws.shop.entity.Member;
import com.ws.shop.entity.Order;
import com.ws.shop.entity.Transaction;
import com.ws.shop.util.JdbcUtil;
import com.ws.shop.util.StringUtil;

@Repository(value = "memberDao")
public class MemberDaoImpl extends BaseDaoImpl implements MemberDao {
    String userfiled = "u.id,u.name,u.mail,u.password,u.realname,u.ip,u.head,u.sex,u.birthday,u.tel,u.score,u.beat,u.pointb,u.pointc,u.priority,u.robot,u.addtime,u.phone,u.old,u.uploadhead,u.state";
	private static Logger log = Logger.getLogger(MemberDaoImpl.class);
	/**
	 * 
	 * 确认反点
	 * 
	 */
	public int rebeat(final long oid, final int uid) {
		return (Integer) this.execute(new ConnectionCallback() {
			public Object doInConnection(Connection connect) {
				PreparedStatement psmt = null;
				ResultSet rs = null;
				try {
					connect.setAutoCommit(false);
					String s = "select id,uid,aid,isrebeat,state,goodsname from jvf_order where id = ? for update";
					psmt = connect.prepareStatement(s);
					psmt.setLong(1, oid);
					rs = psmt.executeQuery();
					Order order = new Order();
					if (rs.next()) {
						order.setId(rs.getInt(1));
						order.setUid(rs.getInt(2));
						order.setAid(rs.getInt(3));
						order.setIsrebeat(rs.getInt(4));
						order.setState(rs.getInt(5));
						order.setGoodsname(rs.getString(6));
						JdbcUtil.close(rs, psmt, null);
					} else {
						connect.rollback();
						return 1; // 订单不存在
					}
					if (uid != order.getUid()) {
						connect.rollback();
						return 2; // 非法操作
					} else {
						if (order.getIsrebeat() != 1 && order.getState() != 0) {
							connect.rollback();
							return 3; // '订单状态不正确！';
						} else {
							// 查询是否是第二名
							int c = fullRebate(connect, order.getAid());
							if (c == 0) {
								connect.commit();
								return 0;
							} else {
								connect.rollback();
								return c;
							}
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
					try {
						connect.rollback();
					} catch (SQLException e1) {
						e1.printStackTrace();
					}
					return 9;
				} finally {
					JdbcUtil.close(rs, psmt, null);
				}
			}
		});
	}

	/**
	 * 
	 * 全额返点
	 * 
	 * @param connect
	 * @param aid
	 * @return
	 * @throws SQLException
	 * 
	 */
	public int fullRebate(Connection connect, long aid) throws SQLException {// 第二名全额返点
		String s = "select aid,uid,goodsname from jvf_auction_log where aid = ?  order by addtime desc limit 1,2 ";
		PreparedStatement psmt = connect.prepareStatement(s);
		psmt.setLong(1, aid);
		ResultSet rs = psmt.executeQuery();
		if (rs.next()) {
			int uid = rs.getInt(2);
			String goodsname = rs.getString(3);
			JdbcUtil.close(rs, psmt, null);
			s = "select id,name from jvf_user where id = ? for update";
			psmt = connect.prepareStatement(s);
			psmt.setLong(1, uid);
			rs = psmt.executeQuery();
			if (rs.next()) {
				String name = rs.getString(2);
				JdbcUtil.close(rs, psmt, null);
				s = "select ifnull(sum(beat),0) as b,ifnull(sum(pointb),0) as p from jvf_auction_log where uid = ? and aid = ? for update ";
				psmt = connect.prepareStatement(s);
				psmt.setLong(1, uid);
				psmt.setLong(2, aid);
				rs = psmt.executeQuery();
				int beatcount = 0;
				int pointbcount = 0;
				int count = 0;
				if (rs.next()) {
					beatcount = rs.getInt(1);
					pointbcount = rs.getInt(2);
				}
				count = beatcount + pointbcount;
				JdbcUtil.close(rs, psmt, null);
				if (count > 0) {
					// 添加交易日志
					// $config = C('SYSCONFIG');
					String remark = "竞拍商品" + goodsname + "第二名全额返点返还" + count
							+ "{$config['pointb_name']}";
					Transaction transaction = new Transaction();
					transaction.setUid(uid);
					transaction.setName(name);
					transaction.setPayment("第二名全额返点");
					transaction.setMoney(0);
					transaction.setBeat(count);
					transaction.setRemark(remark);
					int save = saveTransaction(connect, transaction);
					int update = updateUserToPoint(connect, uid, count);
					if (save > 0 && update > 0) {
						return 0;
					} else {
						return 7;
					}
				} else {
					return 4;// 拍点为0
				}
			} else {
				JdbcUtil.close(rs, psmt, null);
				return 6;
			}
		} else {// 没有返点
			JdbcUtil.close(rs, psmt, null);
			return 5;
		}
	}

	public int saveTransaction(Connection connect, Transaction transaction)
			throws SQLException {
		String s = "insert into jvf_transaction(uid,name,payment,money,addtime,adddate,remark,beat) VALUES(?,?,?,?,now(),?,?,?)";
		PreparedStatement psmt = connect.prepareStatement(s);
		psmt.setLong(1, transaction.getUid());
		psmt.setString(2, transaction.getName());
		psmt.setString(3, transaction.getPayment());
		psmt.setLong(4, transaction.getMoney());
		psmt.setLong(5, transaction.getAdddate());
		psmt.setString(6, transaction.getRemark());
		psmt.setLong(7, transaction.getBeat());
		int c = psmt.executeUpdate();
		JdbcUtil.close(null, psmt, null);
		return c;
	}

	public int updateUserToPoint(Connection connect, int uid, long count)
			throws SQLException {
		String s = "update jvf_user set pointb = pointb + ? where id = ?";
		PreparedStatement psmt = connect.prepareStatement(s);
		psmt.setLong(1, count);
		psmt.setLong(2, uid);
		int c = psmt.executeUpdate();
		JdbcUtil.close(null, psmt, null);
		return c;
	}

	public int updateUserPriority(int uid, int priority) {
		try {
			String s = "UPDATE jvf_user SET priority = " + priority
					+ " WHERE uid = " + uid;
			return this.update(s);
		} catch (Exception ex) {
			ex.printStackTrace();
			return -1;
		}
	}

	public int saveAddress(Address address) {
		try {
			String s = "INSERT INTO jvf_address(uid ,NAME ,reciver, phoneno,postcode,address) VALUES(?,?,?,?,?,?)";
			return this.update(s, new Object[] { address.getUid(),
					address.getName(), address.getReciver(),
					address.getPhoneno(),address.getPostcode(),
					address.getAddress() });
		} catch (Exception ex) {
			ex.printStackTrace();
			return -1;
		}
	}

	// 某一用户的 地址的个数
	public int getAddressCount(int uid) {
		try {
			String s = "SELECT IFNULL(COUNT(1),0) FROM jvf_address WHERE uid = "
					+ uid;
			return this.queryForInt(s);
		} catch (Exception ex) {
			ex.printStackTrace();
			return -1;
		}
	}

	// 用户地址
	public Address getAddressById(final long id) {
		String s = "SELECT id ,uid ,name ,reciver, phoneno ,postcode ,address , memo FROM jvf_address WHERE id = ?";
		return this.execute(s, new PreparedStatementCallback<Address>() {
			public Address doInPreparedStatement(PreparedStatement ps)
					throws SQLException, DataAccessException {
				ResultSet rs = null;
				try {
					ps.setLong(1, id);
					rs = ps.executeQuery();
					if (rs.next()) {
						Address address = new Address();
						address.setId(rs.getLong(1));
						address.setUid(rs.getInt(2));
						address.setName(rs.getString(3));
						address.setReciver(rs.getString(4));
						address.setPhoneno(rs.getString(5));
						address.setPostcode(rs.getString(6));
						address.setAddress(rs.getString(7));
						address.setMemo(rs.getString(8));
						return address;
					}
					return null;
				} finally {
					JdbcUtil.closeResultSet(rs);
				}
			}
		});
	}

	// 更新某一用户地址
	public int updateAddressByid(Address address) {
		try {
			String s = "UPDATE jvf_address SET reciver = ?, phoneno=? ,postcode=? ,address =? WHERE id = ?";
			System.out.println("UPDATE jvf_address SET reciver = "+address.getReciver()+", phoneno="+address.getPhoneno()+" ,postcode="+address.getPostcode()+" ,address ="+address.getAddress()+" WHERE id = "+address.getId());
			return this.update(s, new Object[] { address.getReciver(),
					address.getPhoneno(), address.getPostcode(),
					address.getAddress(), address.getId() });
		} catch (Exception ex) {
			ex.printStackTrace();
			return -1;
		}
	}

	// 查用户下的地址
	public List<Address> getAddressByUid(long uid) {
		String s = "SELECT id ,uid ,name ,reciver, phoneno ,postcode ,address , memo FROM jvf_address WHERE uid = "
				+ uid;
		return this.query(s, new ResultSetExtractor<List<Address>>() {
			public List<Address> extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Address> list = new LinkedList<Address>();
				Address address = null;
				while (rs.next()) {
					address = new Address();
					address.setId(rs.getLong(1));
					address.setUid(rs.getInt(2));
					address.setName(rs.getString(3));
					address.setReciver(rs.getString(4));
					address.setPhoneno(rs.getString(5));
					address.setPostcode(rs.getString(6));
					address.setAddress(rs.getString(7));
					address.setMemo(rs.getString(8));
					list.add(address);
				}
				return list;
			}
		});
	}

	// 删除某一地址
	public int delAddressById(long id) {
		try {
			String s = "DELETE FROM jvf_address WHERE id = " + id;
			return this.update(s);
		} catch (Exception ex) {
			ex.printStackTrace();
			return -1;
		}
	}
    
	//打卡
	public int doExpoint(final int uid){
		return (Integer)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							connect.setAutoCommit(false);
							String s = "select name,state from jvf_user where id = "+uid+" for update" ;
							stmt = connect.createStatement();
							rs = stmt.executeQuery(s);
							if(rs.next()){
								String username = rs.getString(1);
								int state = rs.getInt(2);
								JdbcUtil.close(rs, stmt, null);
								if(state==1){
									s = "SELECT id,uid,ADDTIME FROM jvf_punch WHERE TO_DAYS(ADDTIME) = TO_DAYS(NOW()) and uid = " + uid + " for update";
									stmt = connect.createStatement();
									rs = stmt.executeQuery(s);
									if(rs.next()){//存在已经打过卡
										connect.rollback();
										return 3;
									}else{
										JdbcUtil.close(rs, stmt, null);
										int pointb = 200;
										Transaction transaction = new Transaction();
										transaction.setUid(uid);
										transaction.setName(username);
										transaction.setPayment("打卡!");
										transaction.setRemark("用户打卡领取"+pointb+"拍点");
										int trs = saveTransaction(connect,  transaction);
										int ups =  updateUserToPoint(connect, uid, pointb);
										if(trs>0 && ups > 0){
											connect.commit();
											return 0;
										}else{
											connect.rollback();
											return 4;//打卡失败
										}
									}
								}else{
									connect.rollback();
									return 2; //无效用户
								}
							}else{
								connect.rollback();
								return 1;//用户不存在
							}
						}catch (Exception e) {
							e.printStackTrace();
							connect.rollback();
							log.error(e.getMessage(), e);
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
						return null;
					}
				}
		);
	}
	
	/**
	 * 用户注册
	 * @param member
	 * @return
	 */
	public String toRegister(final Member member){
		return (String)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							connect.setAutoCommit(false);
							String s = "select name,mail from  jvf_user where name = '" + member.getName() + "' or mail = '"+member.getMail()+"'  for update" ;
							stmt = connect.createStatement();
							rs = stmt.executeQuery(s);
							if(rs.next()){
								connect.rollback();
						        return "1"; //该用户已经存在
							}else{
								JdbcUtil.close(rs, stmt, null);
								s = "insert into jvf_user(name,mail,password,ip,addtime) values(?,?,?,?,now())";
								PreparedStatement ps = connect.prepareStatement(s);
								ps.setString(1, member.getName());
								ps.setString(2, member.getMail());
								ps.setString(3, member.getPassword());
								ps.setString(4, member.getIp());
								int c = ps.executeUpdate();
								JdbcUtil.close(null, ps, null);
								String frex = member.getIp().replaceAll("\\.", "");
								String strcode = frex + StringUtil.getID();
								s = "insert into jvf_verification(mail,code,type,addtime) values(?,?,?,now())";
								ps = connect.prepareStatement(s);
								ps.setString(1, member.getMail());
								ps.setString(2, strcode);
								ps.setString(3, "verification");
								int ver = ps.executeUpdate();
								JdbcUtil.close(null, ps, null);
								if(c > 0 && ver > 0){
									connect.commit();
									return strcode;
								}else{
									connect.rollback();
									return "2";
								}
							}
						}catch (Exception e) {
							e.printStackTrace();
							connect.rollback();
							log.error(e.getMessage(), e);
							return  "9";
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
					}
				}
		);
	}
	
	
	/**
	 * 修改密码
	 * @param uid
	 * @param oldpassword
	 * @param newpassword
	 * @return
	 */
	public int updateUserPassword(final int uid,final String oldpassword,final String newpassword){
		return (Integer)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							connect.setAutoCommit(false);
							String s = "select password from  jvf_user where id  = "+uid+" password = '"+oldpassword+"' for update" ;
							stmt = connect.createStatement();
							rs = stmt.executeQuery(s);
							if(rs.next()){
								int c = updatePassword(connect,newpassword, uid);
								if(c>0){
									connect.commit();
									return 0;
								}else{
									connect.rollback();
									return 2;//修改失败
								}

							}else{
								connect.rollback();
								return 1;//用户不存在
							}
						}catch (Exception e) {
							e.printStackTrace();
							connect.rollback();
							log.error(e.getMessage(), e);
							return  -1;
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
					}
				}
		);
		
	}
	
	public int updatePassword(Connection connect,String password,int uid) throws SQLException{
		String s = "update jvf_user set password = ? where uid = ?";
		PreparedStatement psmt = connect.prepareStatement(s);
		psmt.setString(1, password);
		psmt.setLong(2, uid);
		int result = psmt.executeUpdate();
		JdbcUtil.close(null, psmt, null);
		return result;
	}
	
	
	//修改基本信息
	public int updateUsers(Member member){
		String s ="update jvf_user set realname= ?, sex = ?, birthday = ? where id = ?";
		return  this.update(s,new Object[]{member.getRealname(),member.getSex(),member.getBirthday(),member.getId()});
	}
	
	protected Object rowMapper(ResultSet rs) throws SQLException {

		return null;
	}

	//根据
	public List<Member>  getRobotByAuctionId(final long aid){
		String s = "select " + userfiled + " from jvf_robot as r left join jvf_user as u on r.uid = u.id where r.aid = ?" ;
		return this.query(s, new ResultSetExtractor<List<Member>>() {
			public List<Member> extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Member> robots = new LinkedList<Member>();
				while(rs.next()) {
					Member user = getMember(rs);
					robots.add(user);
				}
				return robots;
			}
		},aid);
	}
	
	public List<Member> getAllRobots(int robot){
		String s = "select " + userfiled + " from  jvf_user as u where u.robot = ? " ;
		return this.query(s, new ResultSetExtractor<List<Member>>() {
			public List<Member> extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Member> robots = new LinkedList<Member>();
				while(rs.next()) {
					Member user = getMember(rs);
					robots.add(user);
				}
				return robots;
			}
		},robot);
	}

	public Member getMember(ResultSet rs) throws SQLException{
		Member user = new Member();
		user.setId(rs.getInt(1));
		user.setName(rs.getString(2));
		user.setMail(rs.getString(3));
		user.setPassword(rs.getString(4));
		user.setRealname(rs.getString(5));
		user.setIp(rs.getString(6));
		user.setHead(rs.getString(7));
		user.setSex(rs.getInt(8));
		user.setBirthday(rs.getTimestamp(9));
		user.setTel(rs.getString(10));
		user.setScore(rs.getInt(11));
		user.setBeat(rs.getInt(12));
		user.setPointb(rs.getLong(13));
		user.setPointc(rs.getInt(14));
		user.setPriority(rs.getInt(15));
		user.setRobot(rs.getInt(16));
		user.setAddtime(rs.getTimestamp(17));
		user.setPhone(rs.getString(18));
		user.setOld(rs.getInt(19));
		user.setUploadhead(rs.getInt(20));
		user.setState(rs.getInt(21));
		return user;
	}
	

	public List<Member> getChirlds(final int uid){
		String s = "select " + userfiled + " from jvf_user as u  where u.parentid = ?";
		return this.query(s, new ResultSetExtractor<List<Member>>() {
			public List<Member> extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Member> childs = new LinkedList<Member>();
				while(rs.next()) {
					Member user = getMember(rs);
					childs.add(user);
				}
				return childs;
			}
		},uid);
	}
	
	public int winersaysave(final Order order,final String word){
		String sql[] = {
				"insert into jvf_sunpic(uid,oid,name,aid,speech) values("+order.getUid()+","+order.getId()+",'"+order.getName()+"',"+order.getAid()+",'"+word+"')",
				"update jvf_order set state =4 where id = " + order.getId() + " and state=3 "
		};
		this.batchUpdate(sql);
		return 2;
	}
	
	
	
	//获取此晒图图片的用户
	public boolean isUserSunpic(long id,int uid){
		String s = "select s.id from jvf_accessory as ac join jvf_accessory_relation as ar on ac.id = ar.accessoryid join jvf_sunpic as s on s.id = ar.relationid where ac.id = ? and ar.tables ='Sunpic' and s.uid = ?";
		long qid = this.queryForLong(s,new Object[]{id,uid});
		if(qid!=0){
			return true;
		}else{
			return false;
		}
	}
	
	//删除图片和关系
	public int delImg(final long  id){
		return (Integer)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							connect.setAutoCommit(false);
							String s = " delete from jvf_accessory where id = " + id;
							stmt = connect.createStatement();
							stmt.addBatch(s);
							s = "delete from jvf_accessory_relation where accessoryid = " + id;
							stmt.addBatch(s);
							stmt.executeBatch();
							connect.commit();
							return 0;
						}catch (Exception e) {
							e.printStackTrace();
							connect.rollback();
							log.error(e.getMessage(), e);
							return  -1;
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
					}
				}
		);
	}
	
	/**
	 * //列出头像
	 * 
	 * @return
	 */
	public List<Map<String, Object>> memberlogo() {
		String s = "select filename from jvf_head order by  rand() limit 36 ";
		List<Map<String, Object>> personHead = this.queryForList(s);
		return personHead;
	}

	/**
	 * 检查用户名
	 * 
	 * @param name
	 * @return
	 */
	public boolean checkName(final String name) {
		return (Boolean)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							String s = "select id from jvf_user where name= ? ";
							PreparedStatement psmt = connect.prepareStatement(s);
							psmt.setString(1, name);
							rs = psmt.executeQuery();
							if(rs.next()){
								return true;
							}else{
								return false;
							}
						}catch (Exception e) {
							e.printStackTrace();
							log.error(e.getMessage(), e);
							return  true;
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
					}
				}
		);
	}

	
	public int updateHead(final String head, final int uid){
		return (Integer)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							String s = "update jvf_user set head = ? ,uploadhead = 1  where id = ?";
							PreparedStatement psmt = connect.prepareStatement(s);
							psmt.setString(1, head);
							psmt.setLong(2, uid);
							int c = psmt.executeUpdate();
							return c;
						}catch (Exception e) {
							e.printStackTrace();
							log.error(e.getMessage(), e);
							return  -1;
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
					}
				}
		);
	}
	
	
	/**
	 * 
	 * 验证邮箱
	 * 
	 * @param email
	 * 
	 */
	public Object checkEmail(String email) {
		String s = "select mail from jvf_user where mail = ?";
		return this.query(s, new ResultSetExtractor<String>() {
			public String extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if (rs.next()) {
				    String mail = rs.getString(1);
					return mail;
				}
				return null;
			}
		}, email);
	}

	/**
	 * 检查登陆
	 * 
	 * @param email
	 * @param password
	 * @return
	 */
	public Object checkLogin(String email, String password) {
		String s = "select mail,name,password from jvf_user where mail = ? and password = ?";
		return this.queryForObject(s, new Object[] { email, password },
				new RowMapper<Member>() {
					public Member mapRow(ResultSet rs, int value)
							throws SQLException {
						Member user = new Member();
						user.setId(new Integer(rs.getInt("id")));
						user.setName(rs.getString("name"));
						user.setPassword(rs.getString("password"));
						return user;
					}
		});
	}

	/**
	 * 
	 * 入登陆日志表
	 * 
	 * @param log
	 * @return
	 * 
	 */
	public void loginlog(Loginlog log) {
		String s = "insert into jvf_loginlog(uid,name,address,ip,addtime) values(?,?,?,?,now())";
		int uid = log.getUid();
		String name = log.getName();
		String address = log.getAddress();
		String ip = log.getIp();
		this.update(s, new Object[] { uid, name, address, ip });
	}

	
	
	/**
	 * 检查是否登录
	 * 
	 * @param uid
	 * @return
	 */
	public Object islogin(int userid) {
		String s = "select id ,name,mail,password,realname from jvf_user where id = ?";
		return this.queryForObject(s, new Object[] { userid },
				new RowMapper<Member>() {
					public Member mapRow(ResultSet rs, int value)
							throws SQLException {
						Member user = new Member();
						user.setId(new Integer(rs.getInt("id")));
						user.setName(rs.getString("name"));
						user.setMail(rs.getString("mail"));
						user.setPassword(rs.getString("password"));
						return user;
					}
				});
	}

	final String findMemberById_SQL = "select name,mail,password,realname,ip,head,sex,birthday,tel,score,beat,pointb,pointc,priority,robot,addtime,phone,old,uploadhead,state from jvf_user where id=?";

	public Member findMemberById(final int id) {
		return this.query(findMemberById_SQL, new ResultSetExtractor<Member>() {
			public Member extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if (rs.next()) {
					Member user = new Member();
					user.setId(id);
					user.setName(rs.getString(1));
					user.setMail(rs.getString(2));
					user.setPassword(rs.getString(3));
					user.setRealname(rs.getString(4));
					user.setIp(rs.getString(5));
					user.setHead(rs.getString(6));
					user.setSex(rs.getInt(7));
					user.setBirthday(rs.getTimestamp(8));
					user.setTel(rs.getString(9));
					user.setScore(rs.getInt(10));
					user.setBeat(rs.getInt(11));
					user.setPointb(rs.getLong(12));
					user.setPointc(rs.getInt(13));
					user.setPriority(rs.getInt(14));
					user.setRobot(rs.getInt(15));
					user.setAddtime(rs.getTimestamp(16));
					user.setPhone(rs.getString(17));
					user.setOld(rs.getInt(18));
					user.setUploadhead(rs.getInt(19));
					user.setState(rs.getInt(20));
					return user;
				}
				return null;
			}
		}, id);
	}

	final String findUserListByRobot = "";

	public List<Member> findMemberListByRobot(int robot) {
		return this.execute(findUserListByRobot,
				new PreparedStatementCallback<List<Member>>() {
					public List<Member> doInPreparedStatement(
							PreparedStatement ps) throws SQLException,
							DataAccessException {
						return null;
					}
				});
	}

	final String save_sql = "insert into jvf_user(name,mail,password,realname,ip,head,sex,birthday,tel,score,beat,pointb,pointc,priority,robot,addtime,phone,old,uploadhead,state) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),?,?,?,?)";
	final String update_pwd_sql="update jvf_user set password=?,ip=?,head=?,sex=?,birthday=?,tel=?,beat=?,pointb=?,pointc=?,robot=?,state=? where id=?";
	final String update_sql = "update jvf_user set ip=?,head=?,sex=?,birthday=?,tel=?,beat=?,pointb=?,pointc=?,robot=?,state=? where id=?";
	public void saveOrUpdate(Member user) {
		if (user.getId() == 0) {
			this.update(save_sql, user.getName(), user.getMail(), user
					.getPassword(), user.getRealname(), user.getIp(), user
					.getHead(), user.getSex(), user.getBirthday(), user
					.getTel(), user.getScore(), user.getBeat(), user
					.getPointb(), user.getPointc(), user.getPriority(), user
					.getRobot(), user.getPhone(), user
					.getOld(), user.getUploadhead(), user.getState());
		} else if(StringUtil.isEmpty(user.getPassword())){
			this.update(update_sql, user.getIp(), user
					.getHead(), user.getSex(), user.getBirthday(), user
					.getTel(), user.getBeat(), user.getPointb(), user
					.getPointc(), user.getRobot(), user.getState(), user
					.getId());
		}else{	
			this.update(update_pwd_sql, user.getPassword(), user.getId(), user
					.getHead(), user.getSex(), user.getBirthday(), user
					.getTel(), user.getBeat(), user.getPointb(), user
					.getPointc(), user.getRobot(), user.getState(), user
					.getId());
		}
	}

	final String findUserListPage_count = "select count(1) from jvf_user";
	final String findUserListPage_list = "select id,name,mail,password,realname,ip,head,sex,birthday,tel,score,beat,pointb,pointc,priority,robot,addtime,phone,old,uploadhead,state from jvf_user";
	final String findUserListPage_order = " order by id desc";

	public Page findMemberListPage(int pageno, int pagesize, String name,
			String mail, int robot) {
		StringBuilder query = new StringBuilder(" where 1=1");
		if (name != null)
			query.append(" and name like '%").append(name).append("%'");
		if (mail != null)
			query.append(" and mail like '%").append(mail).append("%'");
		query.append(" and robot=").append(robot);
		return findPage(pageno, pagesize, new StringBuilder(
				findUserListPage_count).append(query).toString(),
				new StringBuilder(findUserListPage_list).append(query).append(
						findUserListPage_order));
	}

	final String findHeadRandList = "select filename from jvf_head order by rand() limit ?";

	public List<Map<String, Object>> findHeadRandList(int size) {
		return this.queryForList(findHeadRandList, size);
	}

	public Member findMemberByName(final String username) {
		String s = "select id,mail,password,realname,ip,head,sex,birthday,tel,score,beat,pointb,pointc,priority,robot,addtime,phone,old,uploadhead,state,acttime from jvf_user where name=?";
		return this.query(s, new ResultSetExtractor<Member>() {
			public Member extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if (rs.next()) {
					Member user = new Member();
					user.setName(username);
					user.setId(rs.getInt(1));
					user.setMail(rs.getString(2));
					user.setPassword(rs.getString(3));
					user.setRealname(rs.getString(4));
					user.setIp(rs.getString(5));
					user.setHead(rs.getString(6));
					user.setSex(rs.getInt(7));
					user.setBirthday(rs.getTimestamp(8));
					user.setTel(rs.getString(9));
					user.setScore(rs.getInt(10));
					user.setBeat(rs.getInt(11));
					user.setPointb(rs.getLong(12));
					user.setPointc(rs.getInt(13));
					user.setPriority(rs.getInt(14));
					user.setRobot(rs.getInt(15));
					user.setAddtime(rs.getTimestamp(16));
					user.setPhone(rs.getString(17));
					user.setOld(rs.getInt(18));
					user.setUploadhead(rs.getInt(19));
					user.setState(rs.getInt(20));
					user.setActtime(rs.getTimestamp(21));
					return user;
				}
				return null;
			}
		},username);
	}

}
